package com.sinotn.ks.node.export;

import java.io.File;
import java.io.FileOutputStream;
import java.util.zip.CRC32;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.junit.Test;

public class ExcelTpl {
	private File folder=new File("D:\\tcp");
	
	@Test
	public void test01() throws Exception{
		HSSFWorkbook wb=new HSSFWorkbook();
		Sheet sheet=wb.createSheet();
		
		sheet.setColumnWidth(0, 256*8);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(2, 256*5);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*12);
		sheet.setColumnWidth(5, 256*8);
		sheet.setColumnWidth(6, 256*20);
		sheet.setColumnWidth(7, 256*8);
		
		CellStyle cellStyle = wb.createCellStyle(); // 单元格样式
		cellStyle.setWrapText(true);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setAlignment(CellStyle.ALIGN_LEFT);// 水平居左
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中

		CellStyle centerStyle = wb.createCellStyle(); // 单元格样式
		centerStyle.setWrapText(true);
		centerStyle.setBorderBottom(CellStyle.BORDER_THIN);
		centerStyle.setBorderLeft(CellStyle.BORDER_THIN);
		centerStyle.setBorderRight(CellStyle.BORDER_THIN);
		centerStyle.setBorderTop(CellStyle.BORDER_THIN);
		centerStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
		centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
		
		String planName="南部战区陆军保障部2024年度公开招考专业技能类文职人员理论考试";
		int rownum=0;
		Row row=sheet.createRow(rownum++);
		row.setHeight((short)600);
		row.createCell(0).setCellValue(planName+"\r\n 成绩签字确认表");

		short height=400;
		row=sheet.createRow(rownum++);
		row.setHeight(height);
		String beginTime="2024-10-03 08:30:00";
		String endTime="2024-10-03 10:30:00";
		String dayText=beginTime.substring(0, 11);
		if(endTime.startsWith(dayText)){
			endTime=endTime.substring(11);
		}
		if(beginTime.endsWith(":00") && endTime.endsWith(":00")){
			beginTime=beginTime.substring(0, 16);
			endTime=endTime.substring(0, endTime.length()-3);
		}
		String roomName="多媒体01机房";
		row.createCell(0).setCellValue(beginTime+"-"+endTime+"场次");
		row.createCell(6).setCellValue(roomName);

		row=sheet.createRow(rownum++);
		row.setHeight(height);
		Cell cell=row.createCell(0);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("座位号");
		cell=row.createCell(1);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("姓名");
		cell=row.createCell(2);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("性别");
		cell=row.createCell(3);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("身份证号");
		cell=row.createCell(4);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("岗位代码");
		cell=row.createCell(5);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("成绩");
		cell=row.createCell(6);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("签字");
		cell=row.createCell(7);
		cell.setCellStyle(centerStyle);
		cell.setCellValue("备注");
		CRC32 crc32 = new CRC32();
		
		for(int i=0;i<50;i++){
			crc32.update(i);
			row=sheet.createRow(rownum++);
			row.setHeight(height);
			cell=row.createCell(0);
			cell.setCellStyle(centerStyle);
			cell.setCellValue(String.valueOf(i+1));
			cell=row.createCell(1);
			cell.setCellStyle(centerStyle);
			cell.setCellValue("诸葛孔明");
			cell=row.createCell(2);
			cell.setCellStyle(centerStyle);
			cell.setCellValue("男");
			cell=row.createCell(3);
			cell.setCellStyle(centerStyle);
			cell.setCellValue("44178119861127189X");
			cell=row.createCell(4);
			cell.setCellStyle(centerStyle);
			cell.setCellValue("61127189X");
			cell=row.createCell(5);
			cell.setCellStyle(centerStyle);
			cell=row.createCell(6);
			cell.setCellStyle(centerStyle);
			cell=row.createCell(7);
			cell.setCellStyle(centerStyle);
		}
		
		
		// 合并单元格
		CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 7);
		sheet.addMergedRegion(cra);
		RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
		sheet.getRow(0).getCell(0).setCellStyle(centerStyle);

		cra =new CellRangeAddress(1, 1, 0, 5);
		sheet.addMergedRegion(cra);
		RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
		sheet.getRow(1).getCell(0).setCellStyle(centerStyle);

		cra =new CellRangeAddress(1, 1, 6, 7);
		sheet.addMergedRegion(cra);
		RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
		sheet.getRow(1).getCell(6).setCellStyle(centerStyle);
		
		// 页眉
		sheet.getHeader().setLeft("核验码："+Long.toHexString(crc32.getValue()).toUpperCase());
		// 页脚
		Footer footer=sheet.getFooter();
		footer.setLeft("注：请仔细核对信息及成绩，确认无误后请在签字栏签字");
		footer.setRight(HeaderFooter.page()+"/"+HeaderFooter.numPages());
		// 分页表头行
		cra =new CellRangeAddress(2, 2, 0, 7);
		sheet.setRepeatingRows(cra);
		
		File file=new File(folder, planName+roomName+"成绩签字确认表.xls");
		FileOutputStream fos=new FileOutputStream(file);
		wb.write(fos);
		fos.close();
		fos=null;
	}
}
